/**
	AUTHOR		: GANDHI
	DATE		: 16 APRIL, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET BASE PRODUCTS(PRODUCT FAMILY)REVENUE DETAILS BASED ON SEARCH CRITERIA. 

	Exec sp_revenue_summary_report 0, '', 'REQUESTED', '', '', '2', '2005'
*/


alter           proc sp_revenue_summary_report

@product_Id int,
@defaultPrompt varchar(50),
@category varchar(15),
@date_from_input varchar(50),
@date_to_input varchar(50),
@quarter_input varchar(10),
@year_input varchar(10)

as
begin

	declare @finished_part_Number varchar(50)
	declare @part_Number varchar(50)
	declare @schedule_Date datetime
	declare @amount float
	declare @status varchar(50)
	declare @customer_Name varchar(65)
	declare @month varchar(10)
	declare @sales_rep varchar(50)
	declare @end_user varchar(65)

	declare @LineItem_ID int
	declare @Unit_Price float	
	declare @Shipped_Qty int
	declare @Shipped_Amount float
		
	declare @revised_date datetime 
	declare @requested_date datetime 

	declare @req_date datetime
	declare @date_From varchar(10)
	declare @date_To varchar(10)
	declare @dateDiff datetime
	

	declare @base_product_name varchar(50)
	declare @process_stepid bigint
	declare @product_type varchar(50)
	declare @product_type_order varchar(50)
	declare @finished_product_Id int
	declare @baseProductId int

	declare @quantity  int
	declare @price float

	DECLARE @TEMP_CSLIT_ID INT
	DECLARE @TEMP_SHIPPED_PART_NO VARCHAR(200)
	DECLARE @TEMP_PRODUCT_ID INT
	DECLARE @CHK_PRODUCT_FOR_BOARD_OR_KIT VARCHAR(50)
	DECLARE @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP VARCHAR(50)

	DECLARE @LOT_ID INT	
	DECLARE @LOT_PRODUCT_ID INT
	DECLARE @LOT_PARENT_PRODUCT_ID INT
	DECLARE @PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LINEITEM_PRODUCT_PARENT_PRODUCT_ID INT
	DECLARE @LOT_SHIPPED_DATE DATETIME
	DECLARE @LOT_SHIPPED_QTY BIGINT
	DECLARE @LOT_BASEPRODUCTNAME VARCHAR(50)
	DECLARE @LOT_BASE_PRODUCT_ID VARCHAR(20)
	DECLARE @LOT_PROCESS_STEP_ID INT
	DECLARE @TEMP_LOT_PRODUCT_ID INT
	DECLARE @LOT_PRODUCT VARCHAR(50)
	DECLARE @LOT_PS_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE  VARCHAR(50)
	DECLARE @LOT_PRODUCT_TYPE_ID INT
	DECLARE @LINEITEM_PRODUCT_ID INT	
	DECLARE @TEMP_UNIT_COST FLOAT	

	declare @tempDate datetime
	
	if( @quarter_input = 'all' or @quarter_input = '1' )
	begin
		set @tempDate = convert(datetime,@year_input+'-01-01 00:00:00.000')		
	end
	if( @quarter_input = '2' )
	begin
		set @tempDate = convert(datetime,@year_input+'-04-01 00:00:00.000')		
	end
	if( @quarter_input = '3' )
	begin
		set @tempDate = convert(datetime,@year_input+'-07-01 00:00:00.000')		
	end
	if( @quarter_input = '4' )
	begin
		set @tempDate = convert(datetime,@year_input+'-10-01 00:00:00.000')		
	end

	declare @results Table( BaseProductName varchar(50),BaseProductId int,ProductId int,ProductTypeOrder int,ProductType varchar(50),PartNumber varchar(50),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float)--, CslitId int, shippedPartNumber varchar(200))
	declare @base_results Table( BaseProductName varchar(50),BaseProductId int,ProductId int,ProductTypeOrder int,ProductType varchar(50),PartNumber varchar(50),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float)
	declare @final_results Table( BaseProductName varchar(50),BaseProductID int,ProductId int,ProductTypeOrder int,ProductType varchar(50),PartNumber varchar(50),RequestedScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float, Month int, QtStart int, QtEnd int, Year int)
	declare @search_results_table Table( BaseProductName varchar(50),BaseProductId int,ProcessStepId int,PartNumber varchar(50),ProductId int,ProductType varchar(50),ProductTypeOrder int )
	
	declare @CoolCursor CURSOR
	declare @CsolCursor CURSOR
	declare @RmaItemCursor cursor
	DECLARE @LOT_NUMBER_CURSOR CURSOR

	if (@product_Id <> '0')
	begin
		declare ProductCursor CURSOR for
		SELECT  ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END ) 
		AS BASEPRODUCTNAME, 
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND ( P_PARENT_PRODUCT_ID = @product_Id or P.PRODUCT_ID = @product_Id )
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME='FINISHED GOODS'
		ORDER BY P_Type_order, BASEPRODUCTNAME

		INSERT INTO @SEARCH_RESULTS_TABLE 
		SELECT  ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END ) 
		AS BASEPRODUCTNAME, 
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND ( P_PARENT_PRODUCT_ID = @product_Id or P.PRODUCT_ID = @product_Id )
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME='FINISHED GOODS'
		ORDER BY P_Type_order, BASEPRODUCTNAME
	end
	else if (@product_Id = '0')
	begin

		declare ProductCursor CURSOR for
		SELECT   ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END ) AS BASEPRODUCTNAME,
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME='FINISHED GOODS' AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT')
		ORDER BY  P_Type_order, BASEPRODUCTNAME


		INSERT INTO @SEARCH_RESULTS_TABLE 
		SELECT   ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
			END ) AS BASEPRODUCTNAME,
		CASE 
				WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID
				WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID
		END ,
		P.PROCESSSTEP_ID, PART_NUMBER, P.PRODUCT_ID,
		CASE 
		WHEN P_TYPE IS NULL THEN 'CHIP'
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 'BOARD'
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 'KIT'
		ELSE ''
		END AS P_TYPE,
		CASE 
		WHEN P_TYPE IS NULL THEN 1
		WHEN P_TYPE LIKE LTRIM(RTRIM('BOARD')) THEN 2
		WHEN P_TYPE LIKE LTRIM(RTRIM('KIT')) THEN 3
		else 4
		END AS P_TYPE_ORDER
		FROM PRODUCT P, PROCESS_STEP WHERE
		P.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID
		AND P_PARENT_PRODUCT_ID IS NOT NULL 
		AND PS_TYPE_NAME='FINISHED GOODS' AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT')
		ORDER BY  P_Type_order, BASEPRODUCTNAME
	end
	
	open ProductCursor 
	fetch next from ProductCursor
	into @base_product_name, @baseProductId, @process_stepid, @finished_part_Number, @finished_product_Id, @product_type, @product_type_order
	while @@fetch_status = 0
	begin
			
		insert into @base_results(BaseProductName,BaseProductId,ProductId,ProductTypeOrder,ProductType,PartNumber,ScheduleDate,Amount,Status,Type, Quantity, Price) values(@base_product_name,@baseProductId,@finished_product_Id,@product_type_order,@product_type,@finished_part_Number,@tempDate, 0.0, '', 'COOL', 0, 0.0) 							

		-- For Shipped Details	
		SET @CsolCursor = CURSOR for
		select P.Part_Number, C.C_Name,
		CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER, CSLIT.CSLIT_Qty_Shipped, COOL.COOL_Unit_Cost, CSLIT.CSLIT_ID, ISNULL(CSLIT.CSLIT_Shipped_Part_Number, ''), P.Product_ID 		
		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C ,
		     Customer_Shipping_Table CST

		where
		CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		and COOL.Product_ID = @finished_product_Id
                and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
                and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED' 
		AND CONVERT(VARCHAR,DATEPART(YYYY,CST_Ship_Date)) = @YEAR_INPUT
		and so_is_consignment = 1

		-- For Backlog Details		
		if (@category = 'REQUESTED')                
        	begin
			SET @CoolCursor = CURSOR for
			select product.Part_Number,customer.C_Name,
			COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER, cool_qty, cool_unit_cost			
			from product, 
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			and COOL.Product_ID= @finished_product_Id
		end
		else if (@category = 'SCHEDULE')
		begin
			SET @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER,		
                        COOL.COOL_Requested_Date, cool_qty, cool_unit_cost	
			from product, 
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			and COOL.Product_ID= @finished_product_Id
			and (cool.cool_status = 'approved'  or  cool.cool_status = 'hold')
		end

		SET @TEMP_CSLIT_ID		= NULL
		SET @TEMP_SHIPPED_PART_NO	= NULL
		SET @TEMP_PRODUCT_ID		= NULL
			
		-- FOR SHIPPED DETAILS
		open @CsolCursor 
		fetch next from @CsolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price, @TEMP_CSLIT_ID, @TEMP_SHIPPED_PART_NO, @TEMP_PRODUCT_ID 
		while @@fetch_status = 0
		begin
			SET @PRODUCT_PARENT_PRODUCT_ID	= NULL
			if(@amount is null)
			begin
				set @amount = 0
			end
		
			if(@schedule_Date is null OR @schedule_Date= '')
			begin
				set @schedule_Date = @requested_date
			end

			SELECT @PRODUCT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
			FROM PRODUCT WHERE PRODUCT_ID = @TEMP_PRODUCT_ID				

			SELECT @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP = CASE WHEN P_TYPE IS NULL THEN 'CHIP' WHEN P_TYPE LIKE 'BOARD' THEN 'BOARD' WHEN P_TYPE LIKE 'KIT' THEN 'KIT' END 
			FROM PRODUCT WHERE PRODUCT_ID = @finished_product_Id
			
			IF( @TEMP_SHIPPED_PART_NO = '' OR @CHK_PRODUCT_FOR_BOARD_KIT_OR_CHIP = 'CHIP'  )
			BEGIN
				insert into @results(BaseProductName,BaseProductId,ProductId,ProductTypeOrder,ProductType,PartNumber,ScheduleDate,Amount,Status,Type, Quantity, Price) values(@base_product_name,@baseProductId,@finished_product_Id,@product_type_order,@product_type,@finished_part_Number,@schedule_Date, @amount, @status, 'COOL', @quantity, @price) 	
			END
			ELSE
			BEGIN
				SET @LOT_ID			= NULL
				SET @LOT_PRODUCT_ID		= NULL
				SET @LOT_PARENT_PRODUCT_ID 	= NULL
				SET @LOT_SHIPPED_QTY 		= NULL
				SET @LOT_BASEPRODUCTNAME  = NULL
				SET @LOT_BASE_PRODUCT_ID  = NULL
				SET @LOT_PROCESS_STEP_ID  = NULL
				SET @TEMP_LOT_PRODUCT_ID  = NULL
				SET @LOT_PRODUCT = NULL
				SET @LOT_PS_TYPE = NULL
				SET @LOT_PRODUCT_TYPE = NULL
				SET @LOT_PRODUCT_TYPE_ID = NULL

				SET @LOT_NUMBER_CURSOR = CURSOR FOR SELECT CSLNT.Lot_ID, P.Product_ID, CSLNT.CSLNT_Qty_Pull  FROM Customer_Shipping_Line_Item_Table CSLIT 
				INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON CSLIT.CSLIT_ID = CSLNT.CSLIT_ID 
				INNER JOIN Lot_Table LT ON CSLNT.Lot_ID = LT.Lot_ID 
				INNER JOIN Product P ON LT.ProcessStep_ID = P.ProcessStep_ID
				WHERE CSLNT.CSLIT_ID = @TEMP_CSLIT_ID
							
				OPEN @LOT_NUMBER_CURSOR 
				FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_ID, @LOT_PRODUCT_ID, @LOT_SHIPPED_QTY 
				WHILE @@FETCH_STATUS = 0
				BEGIN
							
					SELECT @LOT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
					FROM PRODUCT WHERE PRODUCT_ID = @LOT_PRODUCT_ID
					
					IF( @LOT_PARENT_PRODUCT_ID = @PRODUCT_PARENT_PRODUCT_ID )
					BEGIN
						INSERT INTO @RESULTS VALUES( @base_product_name,@baseProductId,@finished_product_Id,@product_type_order,@product_type,@finished_part_Number,@schedule_Date, @LOT_SHIPPED_QTY*@price, @status, 'COOL', @LOT_SHIPPED_QTY, @price )
					END
					ELSE
					BEGIN
						SELECT @LOT_BASEPRODUCTNAME = BaseProductName, @LOT_BASE_PRODUCT_ID = BaseProductID , @LOT_PROCESS_STEP_ID = ProcessStepID, @TEMP_LOT_PRODUCT_ID = ProductID, @LOT_PRODUCT = PartNumber,
						@LOT_PRODUCT_TYPE = ProductType, @LOT_PRODUCT_TYPE_ID = ProductTypeOrder
						FROM  @SEARCH_RESULTS_TABLE WHERE ProductID = ( SELECT P.Product_ID FROM Product P INNER JOIN Lot_Table LT ON P.ProcessStep_ID = LT.ProcessStep_ID WHERE LT.Lot_ID = @LOT_ID )
						
						IF @TEMP_LOT_PRODUCT_ID IS NOT NULL 
						BEGIN
							INSERT INTO @RESULTS VALUES( @LOT_BASEPRODUCTNAME, @LOT_BASE_PRODUCT_ID, @TEMP_LOT_PRODUCT_ID, @LOT_PRODUCT_TYPE_ID, @LOT_PRODUCT_TYPE, @LOT_PRODUCT,
							@schedule_Date, @LOT_SHIPPED_QTY*@price, @status, 'COOL', @LOT_SHIPPED_QTY, @price )
						
						END
					END					
		
					SET @LOT_ID			= NULL
					SET @LOT_PRODUCT_ID		= NULL
					SET @LOT_PARENT_PRODUCT_ID 	= NULL
					SET @LOT_SHIPPED_QTY 		= NULL
					SET @LOT_BASEPRODUCTNAME  = NULL
					SET @LOT_BASE_PRODUCT_ID  = NULL
					SET @LOT_PROCESS_STEP_ID  = NULL
					SET @TEMP_LOT_PRODUCT_ID  = NULL
					SET @LOT_PRODUCT = NULL
					SET @LOT_PS_TYPE = NULL
					SET @LOT_PRODUCT_TYPE = NULL
					SET @LOT_PRODUCT_TYPE_ID = NULL
		
					FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_ID, @LOT_PRODUCT_ID, @LOT_SHIPPED_QTY 
				END
				CLOSE @LOT_NUMBER_CURSOR
				DEALLOCATE @LOT_NUMBER_CURSOR
			END

			set @part_number = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @requested_date = null
			set @quantity = null
			set @price = null
			set @customer_Name = null
			set @sales_rep = null
			set @end_user = null		

			fetch next from @CsolCursor into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user, @quantity, @price, @TEMP_CSLIT_ID, @TEMP_SHIPPED_PART_NO, @TEMP_PRODUCT_ID
		end
		close @CsolCursor
		deallocate @CsolCursor
		
		/*
		GETTING BOARDS OR KITS SUBSTITUTABLE PRODUCT SHIPPED PULLED QTY.
		*/	
		
		IF ( @product_Id <> '0' AND @baseProductId = @finished_product_id)
		BEGIN
			SET @LOT_PRODUCT_ID 		= NULL
			SET @LOT_SHIPPED_DATE 		= NULL
			SET @LOT_SHIPPED_QTY 		= NULL
			SET @LINEITEM_PRODUCT_ID 	= NULL
			SET @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = NULL
			SET @TEMP_UNIT_COST		= NULL
			
			SET @LOT_NUMBER_CURSOR = CURSOR FOR SELECT P.Product_ID, CST.CST_Ship_Date, CSLNT.CSLNT_Qty_Pull,
			(SELECT P.Product_ID FROM Customer_Open_Order_LineItem COOL INNER JOIN Product P ON COOL.Product_ID = P.Product_ID WHERE COOL.LineItem_ID = CSLIT.LineItem_ID) AS LineItem_ProductID,
			COOL.COOL_Unit_Cost
			FROM Customer_Shipping_Lot_Number_Table CSLNT 
			INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID 
			INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
			INNER JOIN Sales_Order SO ON CST.SalesOrder_ID = SO.SalesOrder_ID 
			INNER JOIN Lot_Table LT ON CSLNT.Lot_ID = LT.Lot_ID 
			INNER JOIN Product P ON LT.ProcessStep_ID = P.ProcessStep_ID
			INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID
			WHERE (SO.SO_Is_Sample = 1) AND (CST.CST_Status = 'SHIPPED') AND (CSLNT.CSLNT_Qty_Pull > 0) AND P.Product_ID = @finished_product_id AND P.P_Type IS NOT NULL AND YEAR( CST.CST_Ship_Date ) = @YEAR_INPUT and so_is_consignment = 1
			
			OPEN @LOT_NUMBER_CURSOR 
			FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_PRODUCT_ID, @LOT_SHIPPED_DATE, @LOT_SHIPPED_QTY, @LINEITEM_PRODUCT_ID, @TEMP_UNIT_COST 
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = CASE WHEN P_Parent_Product_ID <> -1 THEN P_Parent_Product_ID WHEN P_Parent_Product_ID = -1 THEN Product_ID END 
				FROM PRODUCT WHERE PRODUCT_ID = @LINEITEM_PRODUCT_ID

				SELECT @finished_part_Number = ISNULL( PART_NUMBER, @finished_part_Number ) FROM PRODUCT WHERE PRODUCT_ID = ( SELECT PRODUCT_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LineItem_ID = @LINEITEM_PRODUCT_ID )

				IF( @LINEITEM_PRODUCT_PARENT_PRODUCT_ID <> @LOT_PRODUCT_ID )
				BEGIN
 					INSERT INTO @RESULTS VALUES( @base_product_name,@baseProductId,@finished_product_Id,@product_type_order,@product_type,@finished_part_Number,@LOT_SHIPPED_DATE, @LOT_SHIPPED_QTY * @TEMP_UNIT_COST, 'SHIPPED', 'COOL', @LOT_SHIPPED_QTY, @TEMP_UNIT_COST	)
				END
				
				SET @LOT_PRODUCT_ID 		= NULL
				SET @LOT_SHIPPED_DATE 		= NULL
				SET @LOT_SHIPPED_QTY 		= NULL
				SET @LINEITEM_PRODUCT_ID 	= NULL
				SET @LINEITEM_PRODUCT_PARENT_PRODUCT_ID = NULL
				SET @TEMP_UNIT_COST		= NULL
				

				FETCH NEXT FROM @LOT_NUMBER_CURSOR INTO @LOT_PRODUCT_ID, @LOT_SHIPPED_DATE, @LOT_SHIPPED_QTY, @LINEITEM_PRODUCT_ID, @TEMP_UNIT_COST 
			END
			CLOSE @LOT_NUMBER_CURSOR
			DEALLOCATE @LOT_NUMBER_CURSOR
		END
		
		-- FOR BACKLOG DETAILS
		open @CoolCursor 
		if (@category = 'REQUESTED')                
		begin
			fetch next from @CoolCursor
			into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price
		end
		else if (@category = 'SCHEDULE')
		begin
			fetch next from @CoolCursor
			into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date, @quantity, @price
		end	
				
		while @@fetch_status = 0
		begin
			if(@amount is null)
			begin
				set @amount = 0
			end
			if (@category = 'REQUESTED')                
		        begin
				if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
				begin
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
				end
				else
				begin
					set @schedule_Date = @schedule_Date
				end
			end	
			
			if (@category = 'SCHEDULE')
			begin
				if(@schedule_Date is null OR @schedule_Date= '')
				begin
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
				end
				else
				begin
					set @schedule_Date = @schedule_Date
					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
					
			if (@category = 'SCHEDULE')
			begin
				if(@revised_date is not null OR @revised_date <> '')
				begin
					set @schedule_Date = @revised_date
						
					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
		
			select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
					
			set @amount = @amount - (@Unit_Price * @Shipped_Qty)	
			if ( @amount < 0 )
			begin
				set @amount = 0	
			end
				
			set @quantity = @quantity - @shipped_qty
			
			if ( @quantity  < 0)
			begin
				set @quantity = 0
			end
		
			insert into @results(BaseProductName,BaseProductId,ProductId,ProductTypeOrder,ProductType,PartNumber,ScheduleDate,Amount,Status,Type, Quantity, Price) values(@base_product_name,@baseProductId,@finished_product_Id,@product_type_order,@product_type,@finished_part_Number,@schedule_Date, @amount ,@status, 'COOL', @quantity, @price ) 	
		
			set @part_number = null
			set @customer_name = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @LineItem_ID = null
			set @unit_price = null
			set @requested_date = null
			set @revised_date = null
			set @quantity = null
			set @price = null
			set @sales_rep = null
			set @end_user = null	

			if (@category = 'REQUESTED')                
	        	begin
				fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @quantity, @price
			end
			else if (@category = 'SCHEDULE')                
	        	begin
				fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date, @quantity, @price
			end
		end
		close @CoolCursor
		deallocate @CoolCursor
		
		set @base_product_name = null
		set @finished_part_Number = null
		set @process_stepid = null
		set @part_Number = null
		set @finished_product_Id = null
		set @product_type = null
		set @product_type_order = null
		set @baseProductId = null
		
		fetch next from ProductCursor
		into @base_product_name, @baseProductId, @process_stepid, @finished_part_Number, @finished_product_Id, @product_type, @product_type_order

	end
	close ProductCursor
	deallocate ProductCursor	
 
	declare @FinalCursor CURSOR

	declare @month_number int
	declare @quarter_start int
	declare @quarter_end int
	declare @act_year int

	declare @baseProductName varchar(50)
	declare @bPId int
	declare @pId int
	declare @pTypeOrder int
	declare @pType varchar(50)
	declare @pNumber varchar(50)
	declare @cName varchar(50)
	declare @revDate datetime
	declare @revAmount float
	declare @revStatus varchar(15)
	declare @revType varchar(5)
	declare @sRep varchar(50)
	declare @eUser varchar(50)
	declare @revQuantity int
	declare @revUnitPrice float

			
	if( @quarter_input <> 'All')
	begin
		set @FinalCursor = cursor for 
		select * from @results WHERE DATEPART(Q, ScheduleDate ) = @quarter_input and Year(ScheduleDate) = @year_input
	end
	else
	begin
		set @FinalCursor = cursor for 
		select * from @results where Year(ScheduleDate) = @year_input
	end

	open @FinalCursor 
		fetch next from @FinalCursor
		into @baseProductName, @bPId, @pId, @pTypeOrder, @pType, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice
		while @@fetch_status = 0
		begin

 			select @act_year = DATEPART(yyyy, @revDate) 

 			select @month_number = DATEPART(mm, @revDate) 
 			
 			if (@month_number >=1 and @month_number <=3)
 			begin
 				set @quarter_start = 1
 				set @quarter_end =3
 				
 			end
 			else if (@month_number >=4 and @month_number <=6)
 			begin
 				set @quarter_start = 2
 				set @quarter_end =6
		
 			end
 			else if (@month_number >=7 and @month_number <=9)
 			begin
 				set @quarter_start = 3
 				set @quarter_end =9
  				
 			end						
 			else if (@month_number >=10 and @month_number <=12)
 			begin
 				set @quarter_start = 4
 				set @quarter_end =12
  			end

			insert into @final_results values( @baseProductName, @bPId, @pId, @pTypeOrder, @pType, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice, @month_number, @quarter_start, @quarter_end, @act_year )					

			fetch next from @FinalCursor
			into @baseProductName, @bPId, @pId, @pTypeOrder, @pType, @pNumber, @revDate, @revAmount, @revStatus, @revType, @revQuantity, @revUnitPrice
	
		end

	close @FinalCursor
	deallocate @FinalCursor

	declare @final_all_results Table( BaseProductName varchar(50),BaseProductID int,ProductId int,ProductTypeOrder int,ProductType varchar(50),PartNumber varchar(50),RequestedScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float, Month int, QtStart int, QtEnd int, Year int)
	--declare @final_all_results Table( BaseProductName varchar(50),BaseProductId int,ProductId int,ProductTypeOrder int,ProductType varchar(50),PartNumber varchar(50),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), Quantity int, Price float)

	declare @tmp_product_id int
	declare @temp_date datetime
	declare @temp_status varchar(15)
	declare @temp_count int


	insert into @final_all_results select * from @final_results  

	set @FinalCursor = cursor for select BaseProductName,BaseProductId, ProductId, ProductTypeOrder, ProductType, PartNumber, ScheduleDate from @base_results
	open @FinalCursor 
	fetch next from @FinalCursor into @base_product_name, @baseProductId, @finished_product_Id, @product_type_order, @product_type, @finished_part_Number, @tempDate
	while @@fetch_status = 0
	begin
		select @temp_count = count(*) from @final_results where productid = @finished_product_Id and Status is not null
		if @temp_count = 0 
		begin
			insert into @final_all_results (BaseProductName, BaseProductId, ProductId, ProductTypeOrder, ProductType, PartNumber, RequestedScheduleDate, Amount, Status, Type, Quantity, Price) 
			values(@base_product_name, @baseProductId, @finished_product_Id, @product_type_order, @product_type, @finished_part_Number, @tempDate, 0.0, '', 'COOL', 0, 0.0) 																			
		end
		set @temp_count = null
		fetch next from @FinalCursor into @base_product_name, @baseProductId, @finished_product_Id, @product_type_order, @product_type, @finished_part_Number, @tempDate
	end
	close @FinalCursor
	deallocate @FinalCursor

	
	
	set @FinalCursor = cursor for select ProductId, RequestedScheduleDate from @final_all_results WHERE Status = ''
	
	open @FinalCursor 
		fetch next from @FinalCursor into @tmp_product_id, @temp_date
		while @@fetch_status = 0
		begin

 			select @act_year = DATEPART(yyyy, @temp_date) 

 			select @month_number = DATEPART(mm, @temp_date) 
 			
 			if (@month_number >=1 and @month_number <=3)
 			begin
 				set @quarter_start = 1
 				set @quarter_end =3
 			end
 			else if (@month_number >=4 and @month_number <=6)
 			begin
 				set @quarter_start = 2
 				set @quarter_end =6
		
 			end
 			else if (@month_number >=7 and @month_number <=9)
 			begin
 				set @quarter_start = 3
 				set @quarter_end =9
  				
 			end						
 			else if (@month_number >=10 and @month_number <=12)
 			begin
 				set @quarter_start = 4
 				set @quarter_end =12
  			end

			UPDATE @final_all_results SET Month = @month_number, QtStart = @quarter_start, QtEnd = @quarter_end, Year = @act_year WHERE ProductId = @tmp_product_id AND Status = ''

			fetch next from @FinalCursor into @tmp_product_id, @temp_date		
		end
	close @FinalCursor
	deallocate @FinalCursor
	
	select * from @final_all_results order by ProductTypeOrder, BaseProductName
	--select * from @quarter_table
end